9.1

9.2
USE myDB C-ݿ
--Ĵ洢Ƿڣɾ
IF EXISTS (SELECT name FROM sysobjects  WHERE name = 'proc_Q_stuGrade' AND type = 'P')
   DROP PROCEDURE proc_Q_stuGrade
GO
--洢proc_Q_stuGrade
CREATE PROC proc_Q_stuGrade
@stuName char(20),
@kcname char(20)
AS
if (@stuName is null) 
  print 'ѧ'
else
  if (@kcname is null) 
    print 'γƣ'
  else
    select gradeNum
    from grade
    where sID=(select sID from student where sName=@stuName) and kcID=(select kcID from course where kcName=@kcname)
go

9.3
use myDB
go
--ִд洢̣ѯͬѧġվ衷ſεĳɼ
exec proc_Q_stuGrade '','վ'

9.4
USE myDB --ݿ
GO
--޸Ĵ洢proc_Q_stuGrade
ALTER PROC proc_Q_stuGrade
@stuName char(20),
@kcname char(20)
with encryption
AS
if (@stuName is null) 
  print 'ѧ'
else
  if (@kcname is null) 
    print 'γƣ'
  else
    select gradeNum
    from grade
    where sID=(select sID from student where sName=@stuName) and kcID=(select kcID from course where kcName=@kcname)
go

9.5
use myDB
go
exec sp_rename 'proc_Q_stuGrade','proc_Q_stuGrade_new','OBJECT'

9.6
use myDB
go
DROP PROCEDURE proc_Q_stuGrade_new

9.7
USE myDB
go
EXEC sp_helptext 'proc_Q_stuGrade'

9.8
USE myDB
go
EXEC sp_help 'proc_Q_stuGrade'

9.9
USE myDB
go
EXEC sp_depends 'proc_Q_stuGrade'

9.10

9.11

SQL
use myDB
go
update student 
set sName='_'
where sID='60402'

9.12
CREATE TRIGGER [TRI_jiangchen_ins] ON [dbo].[jiangchen] 
FOR INSERT 
AS
declare @countNum int,@jcRen char(6),@sName varchar(20)
--insertedȡҪӵĽͼ¼ѧѧ
select @jcRen=jcRen from inserted
--studentȡѧ
select @sName=sName from student where sID=@jcRen
--ͳjiangchenǸѧĽͼ¼
select @countNum=count(*) from jiangchen where jcRen=@jcRen and jcLeibie=''
--ͳƵĽж
if @countNum>0 
  print(@sName+'ͬѧѴ '+str(@countNum)+' ּ¼ǿԸѧĽ')

SQL
insert into jiangchen(jcRen,jcLeibie,jcNeirong,jcShijian,jcWenjian) 
values('40108','','δ72ѧʱǹ֣ʱ069079ֹ','2008-04-20','Ժѧ200810ļ')

9.13
use myDB
go
alter trigger TRI_student_update
on student
with encryption
for update 
as
--ַͱ 
declare @sNameOld char(20),@sNameNew char(20)
--insertedȡ޸ĺû
select @sNameNew=sName from inserted
--insertedȡ޸ĺû
select @sNameOld=sName from deleted
print 'ԭѧ '+rtrim(@sNameOld)+' ĳ '+rtrim(@sNameNew)

9.14
use myDB
go 
ALTER TABLE student
DISABLE TRIGGER TRI_student_update

  
9.15
USE myDB
GO
EXEC sp_helptrigger 'jiangchen'
EXEC sp_helptrigger 'jiangchen','INSERT'

9.16
USE myDB
GO 
EXEC sp_helptext 'TRI_student_update'

9.17
EUSE myDB
GO 
EXEC sp_help 'TRI_student_update'

9.18
use myDB
go
DROP TRIGGER TRI_student_update

